<?php 
	class Videos extends CI_Model{

	 	function __construct(){
        	parent::__construct();
    	}

		/**
		* 函数功能介绍：根据ID返回视频详细信息
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-08-20 16:04L
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @return array
		*/
		function get_video_info($id){
			if(empty($id)) return array();
			$sql = "SELECT v.id,v.cate_id,v.cate_id,v.subject_id,v.description,v.course_id,v.title,v.click_num,v.real_click_num,v.base_click,v.add_time,v.edit_time,v.image_url,v.video_url,v.duration,v.sort,v.status,v.comment_count,u.nick FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid ";
			$sql.= " WHERE ";
			if(is_array($id)) {
				$ids = implode(",",$id);
				$sql.= " v.id IN (". $ids .") ";
			}else {
				$sql.= " v.id=".$id."";
			}
			
			$result = $this->db->query($sql)->result_array();
			return $result;
		}

		/**
		* 函数功能介绍：返回修改视频结果
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-09-02 16:20L
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @return array
		*/
		function edit_videos($data){
			$this->db->where('id', $data['id']);
			$result = $this->db->update('video', $data); 
       		return $result;
		}


		/**
		* 函数功能介绍：执行删除视频数据的操作
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-08-21 10:08
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @return array
		*/
		function delete($id){
			$this->db->where('id', $id);
			$result = $this->db->delete('video');
			return $result;
		}


		/**
		* 函数功能介绍：更新视频显示隐藏
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-08-21 10:08
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @return array
		*/
		function update_video_status($id,$data){
			$this->db->where('id', $id);
			$this->db->update('video', $data);
		}

		/**
		* 函数功能介绍：返回视频列表（用于分页显示）
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-08-21 11:08
		* 最后修改人：Zhang ShaoWei
		* 最后修改时间：2014-08-29 15:08
		* @param id int
		* @return array
		*/
		function get_list_ofpage($limit = 0, $offset = 10,$data = 0){
			$where = "1=1";
			if($data['sort'] == 1 and $data['asc_des'] == 2){
				$order = "v.baidu_recommended asc";
			}else if($data['sort'] == 1 and $data['asc_des'] == 1){
				$order = "v.baidu_recommended desc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 2){
				$order = "v.click_num asc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 1){
				$order = "v.click_num desc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 2){
				$order = "v.wap_recommended asc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 1){
				$order = "v.wap_recommended desc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 2){
				$order = "v.add_time asc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 1){
				$order = "v.add_time desc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 2){
				$order = "v.status asc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 1){
				$order = "v.status desc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 2){
				$order = "v.sort asc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 1){
				$order = "v.sort desc";
			}else{
				$order = "v.id desc";
			}
			if(isset($data['aid'])){
				$sql = "SELECT video_id FROM {$this->db->dbprefix}activity  WHERE id=".$data['aid']."";
				$result = $this->db->query($sql)->result_array();
				if($result[0]['video_id']){
					$where.=" and v.id in (".$result[0]['video_id'].")";
				}else{
					$result[0]['video_id'] = 0;
					$where.=" and v.id in (".$result[0]['video_id'].")";
				}
			}

			if(!empty($data['search'])){
				switch ($data['search_type']){
					case 2:
						$where .= " AND v.uid = '". intval($data['search'])."'";
						break;
					case 3:
						$where .= " AND (u.nick LIKE '%".$data['search']."%')";
						break;
					default:
						$where .= " AND (v.title LIKE '%".$data['search']."%')";
						break;
				}
			}

			if($data['review'] && $data['review'] > 0){
				$where .= " AND v.review = '". $data['review']."'";
			}
			
			if($data['cate_id'] && $data['cate_id'] > 0){
				$where .= " AND v.cate_id = '". $data['cate_id']."'";
			}
			
			if(!empty($data['activity_id'])) {
				$where .= " AND v.activity_id = '". $data['activity_id']."'";
			}

			$sql = "SELECT v.id,v.cate_id,v.uid,v.review,v.duration,v.title,v.click_num,v.add_time,v.sort,v.status, u.nick FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			if($data['search']){
				foreach ($result as $key => $value) {
					$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
				}
			}
			$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid WHERE $where";
			$count = $this->db->query($sql)->result();
       		return array("count" => $count[0]->count, "result" => $result);
		}

		/**
		* 函数功能介绍：通过标签返回视频列表（用于分页显示）
		* 创建人: 	Huang YongJun
		* 创建时间: 2015-05-14 10:30
		* 最后修改人：Huang YongJun
		* 最后修改时间：
		* @param id int
		* @return array
		*/
		
		function get_list_video_page($limit = 0, $offset = 10,$data = 0){
			$where = "1=1 and v.status=1 ";
			if($data['sort'] == 1 and $data['asc_des'] == 2){
				$order = "v.baidu_recommended asc";
			}else if($data['sort'] == 1 and $data['asc_des'] == 1){
				$order = "v.baidu_recommended desc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 2){
				$order = "v.click_num asc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 1){
				$order = "v.click_num desc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 2){
				$order = "v.wap_recommended asc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 1){
				$order = "v.wap_recommended desc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 2){
				$order = "v.add_time asc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 1){
				$order = "v.add_time desc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 2){
				$order = "v.status asc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 1){
				$order = "v.status desc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 2){
				$order = "v.sort asc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 1){
				$order = "v.sort desc";
			}else{
				$order = "v.id desc";
			}
			if(isset($data['aid'])){
				$sql = "SELECT video_id FROM {$this->db->dbprefix}activity  WHERE id=".$data['aid']."";
				$result = $this->db->query($sql)->result_array();
				if($result[0]['video_id']){
					$where.=" and v.id in (".$result[0]['video_id'].")";
				}else{
					$result[0]['video_id'] = 0;
					$where.=" and v.id in (".$result[0]['video_id'].")";
				}
			}
			if($data['search']){
				$where .= " AND (title LIKE '%".$data['search']."%')";
			}
			// if(isset($data['aid'])) {
			// 	$where .= ' AND aid = '.$data['aid'];
			// }
			if(isset($data['zid']) && $data['zid'] > 0){
				$where .= ' AND v.cid = ' . $data['zid'];
			}elseif(isset($data['cat']) && $data['cat'] >= 0 && $data['cat'] != 'all'){
				$where .= ' AND v.cid = ' . $data['cat'];
			}

			$sql = "SELECT v.id,v.uid,v.review,v.title,v.baidu_recommended,v.click_num,v.wap_recommended,v.add_time,v.sort,v.status,v.is_live,v.is_wb_recommend,c.id AS c_id,c.pid,c.cat_name FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			//if($data['search']){
				foreach ($result as $key => $value) {
					$result[$key]['title2'] =$value['title'];
					$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
				}
			//}
			$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where";
			$count = $this->db->query($sql)->result();
       		return array("count" => $count[0]->count, "result" => $result);
		}
		
		function get_list_tag_page($limit = 0, $offset = 10,$data = 0){
			$where = "1=1";
			if($data['sort'] == 1 and $data['asc_des'] == 2){
				$order = "v.baidu_recommended asc";
			}else if($data['sort'] == 1 and $data['asc_des'] == 1){
				$order = "v.baidu_recommended desc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 2){
				$order = "v.click_num asc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 1){
				$order = "v.click_num desc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 2){
				$order = "v.wap_recommended asc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 1){
				$order = "v.wap_recommended desc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 2){
				$order = "v.add_time asc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 1){
				$order = "v.add_time desc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 2){
				$order = "v.status asc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 1){
				$order = "v.status desc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 2){
				$order = "v.sort asc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 1){
				$order = "v.sort desc";
			}else{
				$order = "v.id desc";
			}
			if($data['search']){
				$where .= " AND (title LIKE '%".$data['search']."%' OR v.uid = '". $data['search'] ."')";
			}

			if(isset($data['zid']) && $data['zid'] > 0){
				$where .= ' AND v.cid = ' . $data['zid'];
			}elseif(isset($data['cat']) && $data['cat'] > 0 && $data['cat'] != 'all'){
				$where .= ' AND v.cid = ' . $data['cat'];
			}
			if(isset($data['tag_id']) && $data['tag_id'] > 0){
				$where .= ' AND tr.tid = ' . $data['tag_id'];
			}

			$sql = "SELECT v.id,v.uid,v.title,v.baidu_recommended,v.click_num,v.wap_recommended,v.add_time,v.sort,v.status,v.is_live,c.id AS c_id,c.pid,c.cat_name FROM  {$this->db->dbprefix}tag_relation tr left join {$this->db->dbprefix}video as v on(tr.pid=v.id and tr.type=1) left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			foreach ($result as $key => $value) {
				$result[$key]['title2'] = $value['title'];
				if($data['search']){
					$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
				}
			}
			$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}tag_relation tr left join {$this->db->dbprefix}video as v on(tr.pid=v.id and tr.type=1) left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where";
			$count = $this->db->query($sql)->result();
       		return array("count" => $count[0]->count, "result" => $result);
		}

		function get_list_cate_page($limit = 0, $offset = 10,$data = 0){
			$where = "1=1";
			if($data['sort'] == 1 and $data['asc_des'] == 2){
				$order = "v.baidu_recommended asc";
			}else if($data['sort'] == 1 and $data['asc_des'] == 1){
				$order = "v.baidu_recommended desc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 2){
				$order = "v.click_num asc";
			}else if($data['sort'] == 2 and $data['asc_des'] == 1){
				$order = "v.click_num desc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 2){
				$order = "v.wap_recommended asc";
			}else if($data['sort'] == 3 and $data['asc_des'] == 1){
				$order = "v.wap_recommended desc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 2){
				$order = "v.add_time asc";
			}else if($data['sort'] == 4 and $data['asc_des'] == 1){
				$order = "v.add_time desc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 2){
				$order = "v.status asc";
			}else if($data['sort'] == 5 and $data['asc_des'] == 1){
				$order = "v.status desc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 2){
				$order = "v.sort asc";
			}else if($data['sort'] == 6 and $data['asc_des'] == 1){
				$order = "v.sort desc";
			}else{
				$order = "v.id desc";
			}
			if($data['search']){
				$where .= " AND (title LIKE '%".$data['search']."%' OR v.uid = '". $data['search'] ."')";
			}
			
			if(isset($data['zid']) && $data['zid'] > 0){
				$where .= ' AND v.cid = ' . $data['zid'];
			}elseif(isset($data['cat']) && $data['cat'] >= 0 && $data['cat'] != 'all'){
				$where .= ' AND v.cid = ' . $data['cat'];
			}

			$sql = "SELECT v.id,v.uid,v.title,v.baidu_recommended,v.click_num,v.wap_recommended,v.add_time,v.sort,v.status,v.is_live,c.id AS c_id,c.pid,c.cat_name FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where ORDER BY ".$order." LIMIT $limit, $offset";
			$result = $this->db->query($sql)->result_array();
			
			foreach ($result as $key => $value) {
				$result[$key]['title2'] = $value['title'];
				if($data['search']){
					$result[$key]['title'] = str_replace($data['search'],'<font color="red">'.$data['search'].'</font>',$value['title']);
				}
			}
			$sql = "SELECT count(v.id) AS count FROM {$this->db->dbprefix}video as v left join {$this->db->dbprefix}user as u on u.id = v.uid left join {$this->db->dbprefix}category as c on v.cid = c.id WHERE $where";
			$count = $this->db->query($sql)->result();
       		return array("count" => $count[0]->count, "result" => $result);
		}
		
		/**
		* 函数功能介绍：更新视频归属活动
		* 创建人: 	Zhang ShaoWei
		* 创建时间: 2014-08-29 15:08
		* 最后修改人：
		* 最后修改时间：
		* @param aid int
		* @param data array
		* @return array
		*/
		function update_video_by_aid($aid = 0,$data = array()) {
			$this->db->where('aid', $aid);
			$result = $this->db->update('video', $data); 
			return $result;
		}
		/**
		* 函数功能介绍：更新视频信息
		* 创建人: 	Zhang ShaoWei
		* 创建时间: 2014-08-29 16:08
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @param data array
		* @return array
		*/
		function update_video_by_id($video_id = 0,$data = array()) {
			$this->db->where('id', $video_id);
			$result = $this->db->update('video', $data); 
			return $result;
		}
		/**
		* 函数功能介绍：根据活动id得到视频
		* 创建人: 	Zhang ShaoWei
		* 创建时间: 2014-09-01 10:08
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		*/
		function get_video_by_aid($aid = 0) {
			$sql = "SELECT v.id FROM {$this->db->dbprefix}video as v where aid =".$aid;
			$result = $this->db->query($sql)->result_array();
			return $result;
		}

		/**
		* 函数功能介绍：删除视频
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-09-01 10:08
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		*/
		function delete_videos($id){
			$sql = "SELECT id,uid,status,cate_id FROM {$this->db->dbprefix}video where id =".$id;
			$video_info = $this->db->query($sql)->row_array();
			$this->db->where('id', $id);
 			$result=$this->db->delete('video');
 		// 	if($result){
 		// 		//减少用户视频数量
 		// 		if(isset($video_info['uid'])&&$video_info['uid']>0&&$video_info['status']==1){
 		// 			$this->load->model('users');
			// 		$this->users->reduce_user_video_number($video_info['uid']);	
			// 		//减少分类视频数量
			// 		if($video_info['cate_id']>0){
			// 			$this->load->model("m_cate");
			// 			$this->m_cate->reduce_count($video_info['cate_id']);
			// 		}
 		// 		}
 		// 	}
 		// 	//删除视图
			// $this->db->where('top_id', $id);
			// $this->db->where('type', 3);
			// $result=$this->db->delete('relation');
			
 			return $result;
		}


		/**
		* 修改活动数据
		* 创建人: 	Yang Ningshu
		* 创建时间: 2014-09-02 14:59
		* 最后修改人：
		* 最后修改时间：
		* @param id int
		* @param data array
		* @return array
		*/
		function update_video_sort($id,$data){
			$this->db->where('id', $id);
 			return $this->db->update('video', $data);
		}	
		/**
		* 修改活动数据
		* 创建人: 	Huang YongJun
		* 创建时间: 2015-05-14 14:59
		* 最后修改人：
		* 最后修改时间：
		*/
		//取得产品的图片表列
		function get_index_recommend_video($video_ids){
			$sql = "SELECT * FROM {$this->db->dbprefix}video WHERE id IN(".$video_ids.") ORDER BY substring_index('".$video_ids."',id,1)";
			$rslist = $this->db->query($sql)->result_array();
			return $rslist;
		}

		/**
		* 函数功能介绍：增加视频播放次数
		* 创建人: Huang YongJun
		* 创建时间: 2015-5-18 14:59
		* 最后修改人：
		* 最后修改时间：
		*/
		function update_click_number($id){
			$sql = "UPDATE {$this->db->dbprefix}video SET click_num = (if(((ifnull(ceil(((POWER(2,(-0.001*(real_click_num+1)))+2)*(real_click_num+1))+80*LN(real_click_num+1)),0) + base_click)-(ifnull(ceil(((POWER(2,(-0.001*(real_click_num)))+2)*(real_click_num))+80*LN(real_click_num)),0) + base_click))>15,(ifnull(ceil(((POWER(2,(-0.001*(real_click_num+1)))+2)*(real_click_num+1))+80*LN(real_click_num+1)),0) + base_click+FLOOR(0+(RAND()*15))),(ifnull(ceil(((POWER(2,(-0.001*(real_click_num+1)))+2)*(real_click_num+1))+80*LN(real_click_num+1)),0) + base_click+FLOOR(0+(RAND()*((ifnull(ceil(((POWER(2,(-0.001*(real_click_num+1)))+2)*(real_click_num+1))+80*LN(real_click_num+1)),0) + base_click)-(ifnull(ceil(((POWER(2,(-0.001*(real_click_num)))+2)*(real_click_num))+80*LN(real_click_num)),0) + base_click))))))) WHERE id=$id";
			$this->db->query($sql);
		}	



		function get_video_cate(){
			$sql = "SELECT id, name from {$this->db->dbprefix}course_cate";
			$result = $this->db->query($sql)->result_array();
			return $result;
		}

		function get_course_cate(){
			$sql = "SELECT id, title from {$this->db->dbprefix}b_subject";
			$result = $this->db->query($sql)->result_array();
			return $result;
		}

	}